/**
 * File DAOHelper.java created by Pablo Díaz Fernández
 * on 01/04/2012 for the project supply-chain-simulator
 */
package org.lsk.pfc.scsim.test.model.persistence.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.lsk.pfc.scsim.test.model.exceptions.TestConfigurationException;

/**
 * Utility class which provide functionalities to DAO tests.
 */
public final class DAOHelper {

    /**
     * Private constructor to avoid implementation.
     */
    private DAOHelper() {
    }

    /**
     * Gets a database connection.
     * 
     * @return Database connection
     * @throws TestConfigurationException
     *             exception if the connection can not be created.
     */
    public static Connection getConnection() throws TestConfigurationException {
        String dbUrl = "jdbc:mysql://localhost/BullwhipEffect?user=bwe_user";
        String dbClass = "com.mysql.jdbc.Driver";
        try {
            Class.forName(dbClass);
        } catch (ClassNotFoundException e) {
            throw new TestConfigurationException(e);
        }
        try {
            return DriverManager.getConnection(dbUrl);
        } catch (SQLException e) {
            throw new TestConfigurationException(e);
        }
    }

    /**
     * Delete test rows that have id &lt; <code>testLimit</code> from the table
     * specified.
     * 
     * @param con
     *            Database connection.
     * @param table
     *            Table to use.
     * @param testLimit
     *            Limit index used in tests.
     * @throws SQLException
     *             SQL exception deleting data.
     */
    public static void deleteRows(final Connection con, final String table,
            final int testLimit) throws SQLException {
        PreparedStatement s = null;
        StringBuffer sb = new StringBuffer("DELETE FROM ");
        sb.append(table).append(" WHERE ID < ").append(testLimit);
        s = con.prepareStatement(sb.toString());
        s.executeUpdate();
        s.close();
    }

    /**
     * Delete test rows that have id = <code>testLimit</code> from the table
     * specified.
     * 
     * @param con
     *            Database connection.
     * @param table
     *            Table to use.
     * @param id
     *            Id to delete.
     * @throws SQLException
     *             SQL exception deleting data.
     */
    public static void deleteRow(final Connection con, final String table,
            final long id) throws SQLException {
        PreparedStatement s = null;
        StringBuffer sb = new StringBuffer("DELETE FROM ");
        sb.append(table).append(" WHERE ID = ").append(id);
        s = con.prepareStatement(sb.toString());
        s.executeUpdate();
        s.close();
    }

    /**
     * Gets the number of rows in the table. If <code>testLimit</code> is
     * specified the method counts the rows which ID is &lt;
     * <code>testLimit</code>. Otherwise counts all the records.
     * 
     * @param con
     *            Database connection.
     * @param table
     *            Table to use or <code>null</code> if no limit is wanted.
     * @param testLimit
     *            Limit index used in tests.
     * @return number of rows.
     * @throws SQLException
     *             getting db info.
     */
    public static int getNumRows(final Connection con, final String table,
            final Integer testLimit) throws SQLException {
        Statement s = con.createStatement();
        StringBuffer sb = new StringBuffer("SELECT COUNT(ID) FROM ");
        sb.append(table);
        if (testLimit != null) {
            sb.append(" WHERE ID < ").append(testLimit);
        }
        s.executeQuery(sb.toString());
        ResultSet rs = s.getResultSet();
        rs.first();
        return rs.getInt(1);
    }
}
